require('dotenv').config()
// 导入数据库操作模块
const db = require('../../db')

// MARK 首页

// MARK GET / home / data	首页 - 展示接口
exports.data = (req, res) => {
  // 查询employee表总数命名为employeeTotal 
  const employeeQuery = `SELECT COUNT(*) AS employeeTotal FROM employee`;

  // 查询employee中formOfEmployment为1的总数命名regularEmployeeTotal
  const regularEmployeeQuery = `SELECT COUNT(*) AS regularEmployeeTotal FROM employee WHERE formOfEmployment = 1`;

  // 查询employment中roleId为1的总数命名managerTotal
  const managerQuery = `SELECT COUNT(*) AS managerTotal FROM employee WHERE roleId = 1`;

  // 查询department表总数命名为departmentTotal
  const departmentQuery = `SELECT COUNT(*) AS departmentTotal FROM department`;

  // 查询role表总数命名为roleTotal
  const roleQuery = `SELECT COUNT(*) AS roleTotal FROM role`;

  // 查询每个部门的名称和对应的员工总数
  const departmentEmployeeCountQuery = `
    SELECT 
      d.name AS departmentName,
      COUNT(e.id) AS employeeCount
    FROM 
      department d
    LEFT JOIN 
      employee e ON d.id = e.departmentId
    GROUP BY 
      d.name
  `;

  // 执行所有查询
  db.query(employeeQuery, (err1, results1) => {
    if (err1) {
      return res.status(500).json({ success: false, code: 10002, message: '查询员工总数失败', error: err1.message });
    }

    db.query(regularEmployeeQuery, (err2, results2) => {
      if (err2) {
        return res.status(500).json({ success: false, code: 10003, message: '查询正式员工总数失败', error: err2.message });
      }

      db.query(managerQuery, (err3, results3) => {
        if (err3) {
          return res.status(500).json({ success: false, code: 10004, message: '查询经理总数失败', error: err3.message });
        }

        db.query(departmentQuery, (err4, results4) => {
          if (err4) {
            return res.status(500).json({ success: false, code: 10005, message: '查询部门总数失败', error: err4.message });
          }

          db.query(roleQuery, (err5, results5) => {
            if (err5) {
              return res.status(500).json({ success: false, code: 10006, message: '查询角色总数失败', error: err5.message });
            }

            db.query(departmentEmployeeCountQuery, (err6, departmentResults) => {
              if (err6) {
                return res.status(500).json({ success: false, code: 10007, message: '查询部门员工总数失败', error: err6.message });
              }

              // 提取 xAxis 和 yAxis 数据，并去掉第一个元素
              const xAxis = departmentResults.map(row => row.departmentName).slice(1);
              const yAxis = departmentResults.map(row => row.employeeCount).slice(1);

              // 直接返回样例中的socialInsurance和providentFund数据
              const responseData = {
                success: true,
                code: 10000,
                data: {
                  employeeTotal: results1[0].employeeTotal,
                  regularEmployeeTotal: results2[0].regularEmployeeTotal,
                  managerTotal: results3[0].managerTotal,
                  departmentTotal: results4[0].departmentTotal,
                  roleTotal: results5[0].roleTotal,
                  department: {
                    categoryType: "department",
                    category: "组织规模一览",
                    total: results4[0].departmentTotal,
                    emTotal: results1[0].employeeTotal,
                    xAxis: xAxis,
                    yAxis: yAxis
                  }
                },
                message: "执行成功"
              };

              res.status(200).json(responseData);
            });
          });
        });
      });
    });
  });
}
exports.notice = (req, res) => {
  // 查询消息通知数据，并按 createTime 降序排列
  const query = `
    SELECT 
        userId,
        notice,
        createTime
    FROM 
        notice
    ORDER BY createTime DESC
  `;

  db.query(query, (err, results) => {
    if (err) {
      return res.status(500).json({ success: false, code: 10022, message: '查询消息通知失败', error: err.message });
    }

    // 提取所有 userId
    const userIds = results.map(row => row.userId);

    // 查询 employee 表获取 staffPhoto 和 username
    const employeeQuery = `
      SELECT 
        id,
        staffPhoto,
        username
      FROM 
        employee
      WHERE 
        id IN (${userIds.map(() => '?').join(', ')})
    `;

    db.query(employeeQuery, userIds, (err, employeeResults) => {
      if (err) {
        return res.status(500).json({ success: false, code: 10023, message: '查询员工信息失败', error: err.message });
      }

      // 将 employeeResults 转换为 userId -> { staffPhoto, username } 的映射
      const employeeMap = employeeResults.reduce((acc, employee) => {
        acc[employee.id] = {
          staffPhoto: employee.staffPhoto,
          username: employee.username
        };
        return acc;
      }, {});

      // 格式化响应数据，将 staffPhoto 作为 icon 返回，并添加 username
      const data = results.map(row => ({
        icon: employeeMap[row.userId]?.staffPhoto || '', // 使用 staffPhoto 作为 icon
        username: employeeMap[row.userId]?.username || '', // 添加 username
        notice: row.notice,
        createTime: row.createTime
      }));

      res.json({
        success: true,
        code: 10000,
        data: data,
        message: '执行成功'
      });
    });
  });
};
exports.publish = (req, res) => {
  const { notice } = req.body;
  const userId = req.user.id;

  // 获取当前本地时间并格式化为 YYYY-MM-DD HH:MM:SS
  const now = new Date();
  const createTime = now.toLocaleString('zh-CN', {
    year: 'numeric',
    month: '2-digit',
    day: '2-digit',
    hour: '2-digit',
    minute: '2-digit',
    second: '2-digit',
    hour12: false // 使用 24 小时制
  }).replace(/\//g, '-'); // 将斜杠替换为短横线

  // 插入数据到 notice 表
  const insertQuery = `
    INSERT INTO notice (userId, notice, createTime)
    VALUES (?, ?, ?)
  `;
  const values = [userId, notice, createTime];

  db.query(insertQuery, values, (err, result) => {
    if (err) {
      return res.status(500).json({ success: false, code: 10024, message: '发布消息失败', error: err.message });
    }

    res.json({
      success: true,
      code: 10000,
      data: null,
      message: '发布消息成功'
    });
  });
};

/**
 * option = {
  xAxis: {
    type: 'category',
    data: ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun','gui']
  },
  yAxis: {
    type: 'value'
  },
  series: [
    {
      data: [120, 200, 150, 80, 70, 110, 130,33],
      type: 'bar',
      showBackground: true,
      backgroundStyle: {
        color: 'rgba(180, 180, 180, 0.2)'
      }
    }
  ]
};
 */